package si.assign1.manager;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Set;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import si.assign1.model.Category;
import si.assign1.model.Product;
import si.assign1.model.ProductType;
import si.assign1.model.ProductTypeAttribute;
import si.assign1.model.ProductTypeAttributeValue;

public class ProductSectionVerifier {
	private EntityManager em;
	
	public ProductSectionVerifier(EntityManager em) {
		this.em = em;
	}
	
	public void ListAllObjects()
	{	
		System.out.printf("=======List all Objects==================\n");
		
		Query q;
		System.out.printf("---List all Products:------------\n");
		q = em.createNamedQuery("listProducts");
	    List<Product> products = q.getResultList();
	    for (Product p : products) {
	       System.out.printf("%d\t%s\t%d\t%s\t%3.2f\t%3.2f\t%s\n",
	        				  p.getId(),
	    		   			  p.getName(),
	        		          p.getOnHand(),
	        		          p.getProductType(),
	        		          p.getPurchasePrice(),
	        		          p.getSalePrice(),
	        		          p.getSku());
	        }
	      
	    System.out.printf("-----------------\n---List all ProductTypes:------------\n");
	    q = em.createNamedQuery("listProductTypes");
        List<ProductType> productType = q.getResultList();
	    for (ProductType pt: productType) {
	        System.out.printf("%3d\t%-10s \n", 
	        				   pt.getId(),
	        				   pt.getName());
	    }
	    
	    System.out.printf("-----------------\n---List all Categories:--------------\n");
	    q = em.createNamedQuery("listCategories");
	    List<Category> category = q.getResultList();
	    for (Category c: category) {
	        System.out.printf("%3d\t%-10s \n",
	            			        c.getId(),
	            					c.getName());
	    }
	        
	    System.out.printf("-----------------\n---List all ProductTypeAttributes:---\n");
		q = em.createNamedQuery("listProductTypeAttributes");
		List<ProductTypeAttribute> productTypeAttribute = q.getResultList();
		for (ProductTypeAttribute pa: productTypeAttribute) {
		    System.out.printf("%3d\t%c\t%-10s \n",
		        		 			pa.getId(),
		        		 			pa.getIsRequired(),
		        		 			pa.getName());
		}	        
		
		System.out.printf("------------------\n---List all ProductTypeAttributeValues:-\n");
	    q = em.createNamedQuery("listProductTypeAttributeValues");
	    List<ProductTypeAttributeValue> productTypeAttributeValue = q.getResultList();
	    for (ProductTypeAttributeValue pav: productTypeAttributeValue) {
		     System.out.printf("%3d\t%-10s \n",
			       		 			pav.getId(),
			       		 			pav.getValue());
	    }	      	
	}
	
	public void GetProductsWithAllInfo(String typeName)
	{
		/*
		/*
	     * get all products that are from product types
	     * this is to test if setting type relation from product side
	     * makes the relation really established
	     */
		Query q;
		q = em.createNamedQuery("getProductTypesByName");
	    q.setParameter("name", typeName);
	    ProductType productType = (ProductType)q.getSingleResult();
			
		System.out.printf("\n====All products of %s type are:\n",typeName);
		Set<Product> products = productType.getProducts();
		for (Product p: products) {
			System.out.printf(" %s\n", p.getName());
		    Set<ProductTypeAttributeValue> ptavs 
		    		= p.getProductTypeAttributeValues();		    
		   	for (ProductTypeAttributeValue ptav: ptavs) {
		   		String name = ptav.getProductTypeAttribute().getName();
		   		System.out.printf("  %s = '%s'\n", name, ptav.getValue());
		   	}
		    	
		   	Set<Category> cs = p.getCategories();
		   	for (Category c: cs) {
		   		System.out.printf("  >Associated category = '%s', parent = '%s'\n", 
		   				c.getName(), c.getParent().getName());
		    }
		   	
		}		
	}
	
	public static void verifyWithJdbc() {
		Connection conn = null; 
		try {
			conn = DriverManager.getConnection("jdbc:db2://204.90.115.30:5025/DALLAS9:retrieveMessagesFromServerOnGetMessage=true;emulateParameterMetaDataForZCalls=1;", USER, PWD);
			conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			
			ResultSet rs 
			= stmt.executeQuery(
				"SELECT P.ID,P.NAME,  "+                                            
				"       C.PRODUCT_TYPE,C.NAME,      "+                              
				"       T.ID,T.NAME,          "+                                    
				"       PTA.PRODUCT_TYPE_ID,PTA.NAME,PTA.IS_REQUIRED,PTA.ID, "+           
				"       PTAV.PRODUCT_ID,PTAV.PRODUCT_TYPE_ATTRIBUTE_ID,PTAV.VALUE "+
				"FROM   PRODUCTS P,         "+                                     
				"       PRODUCT_TYPES T,  "+                                       
				"       CATEGORIES C,     "+                                        
				"       PRODUCT_TYPE_ATTRIBUTES PTA,  "+                            
				"       PRODUCT_TYPE_ATTRIBUTE_VALUES PTAV   "+                     
				"WHERE  P.PRODUCT_TYPE = T.ID    "+                                 
				"AND    C.PRODUCT_TYPE = T.ID   "+                                  
				"AND    PTA.PRODUCT_TYPE_ID = T.ID  "+                              
				"AND    PTAV.PRODUCT_ID = P.ID   "+                                 
				"AND    PTAV.PRODUCT_TYPE_ATTRIBUTE_ID=PTA.ID  "+
				"AND    PTA.ID = PTAV.PRODUCT_TYPE_ATTRIBUTE_ID"
								);
			String getCols = null;
			
			System.out.printf("The SQL Statement is:===\n\n");
			System.out.printf("SELECT P.ID,P.NAME,  \n"+                                            
					"       C.PRODUCT_TYPE,C.NAME,     \n "+                              
					"       T.ID,T.NAME,         \n "+                                    
					"       PTA.PRODUCT_TYPE_ID,PTA.NAME,PTA.IS_REQUIRED, PTA.ID\n"+           
					"       PTAV.PRODUCT_ID,PTAV.PRODUCT_TYPE_ATTRIBUTE_ID,PTAV.VALUE \n"+
					"FROM   PRODUCTS P,        \n "+                                     
					"       PRODUCT_TYPES T,  \n"+                                       
					"       CATEGORIES C,     \n"+                                        
					"       PRODUCT_TYPE_ATTRIBUTES PTA,  \n"+                            
					"       PRODUCT_TYPE_ATTRIBUTE_VALUES PTAV   \n"+                     
					"WHERE  P.PRODUCT_TYPE = T.ID   \n "+                                 
					"AND    C.PRODUCT_TYPE = T.ID   \n"+                                  
					"AND    PTA.PRODUCT_TYPE_ID = T.ID  \n"+                              
					"AND    PTAV.PRODUCT_ID = P.ID   \n"+                                 
					"AND    PTAV.PRODUCT_TYPE_ATTRIBUTE_ID=PTA.ID  \n"+ 
					"AND    PTA.ID = PTAV.PRODUCT_TYPE_ATTRIBUTE_ID \n"
					);
			System.out.printf("\nTABLES\n");
			System.out.printf("----------------------------------------------------------------"+
					          "---------------------------------------------------------------\n");
			System.out.printf("PRODUCTS            | "+                                     
					          "CATEGORIES          | "+
					          "PRODUCT_TYPES   | "+                                                                               
					          "PT_ATTRIBUTES\t\t       | "+                            
					          "PTA_VALUES \n");
			System.out.printf("ID\t  NAME\t    | "+                                            
							  "TYPE_ID\tNAME\t  | "+                              
							  "ID\t  NAME\t    | "+                                    
							  "TYPE_ID\tNAME\tREQUIRED  ID   | "+           
					  		  "PRODUCT_ID PT_ATTRI_ID VALUE\t| \n");
			System.out.printf("----------------------------------------------------------------"+
	                         "---------------------------------------------------------------\n");
			 while(rs.next())
		        {	
		            getCols=rs.getString(1);
		            System.out.printf("%-10s", getCols);
		            getCols=rs.getString(2);
		            System.out.printf("%-10s| ", getCols);
		            getCols=rs.getString(3);
		            System.out.printf("%-10s", getCols);
		            getCols=rs.getString(4);
		            System.out.printf("%-10s| ", getCols);
		            getCols=rs.getString(5);
		            System.out.printf("%-6s", getCols);
		            getCols=rs.getString(6);
		            System.out.printf("%-10s| ", getCols);
		            getCols=rs.getString(7);
		            System.out.printf("%-10s",getCols);
		            getCols=rs.getString(8);
		            System.out.printf("%-10s", getCols);
		            getCols=rs.getString(9);
		            System.out.printf("%-8s", getCols);
		            getCols=rs.getString(10);
		            System.out.printf("%-5s| ", getCols);
		            getCols=rs.getString(11);
		            System.out.printf("%-10s", getCols);
		            getCols=rs.getString(12);
		            System.out.printf("%-10s", getCols);
		            getCols=rs.getString(13);
		            System.out.printf("%-10s\n", getCols);
		        }
			 System.out.printf("\n\n");
			conn.commit();
			
			//System.exit(1);
		} catch (SQLException e) {
			if (conn != null) {
				try {
					conn.rollback();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			}
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}				
			}
		}
	}
	
	private static final String USER = "GOLS051";
	private static final String PWD = "longaz";
}
